﻿using System;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;
using System.Web.Script.Serialization;
using SocketBook7Server.Script.logic;

namespace SocketBook7Server.db
{
    class DbManager
    {
        public static MySqlConnection mySql;
        static JavaScriptSerializer Js = new JavaScriptSerializer();
        public static bool Connect(string db, string ip, int port, string user, string pw)
        {
            //mySql = new MySqlConnection();
            //连接参数
            string s = string.Format("Database={0};Data Source={1}; port={2};User Id={3};Password={4}",
                db, ip, port, user, pw);
            //mySql.ConnectionString = s;
            //看官方文档教程的写法、
            string connStr = "server=localhost;user=root;database=game;port=3306;password=hz88858&";
            mySql = new MySqlConnection(connStr);

            try
            {
                mySql.Open();
                Console.WriteLine("[数据库] conncet succ");
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] connect fail :" + e.Message);
                return false;
            }
        }
        //判定安全字符串
        private static bool IsSafeString(string str)
        {
            //指示 Regex 构造函数中指定的正则表达式在指定的输入字符串中是否找到了匹配项。
            return !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\}|\{|%|@|\*|!|\']");
        }
        //是否讯在该用户
        public static bool IsAccountExist(string id)
        {
            //防sql注入
            if (!DbManager.IsSafeString(id)) { return false; }
            string s = string.Format("select*from account where id='{0}';", id);
            //查询
            try
            {
                MySqlCommand cmd = new MySqlCommand(s, mySql);
                //Finishes asynchronous execution of a SQL statement, returning the requested MySqlDataReader.
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool hasRows = dataReader.HasRows;
                dataReader.Close();
                return !hasRows;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] IsSafeString err, " + e.Message);
                return false;
            }
        }
        //注册 实际是对数据库执行插入操作
        public static bool Register(string id, string pw)
        {
            //防sql注入
            if (!DbManager.IsSafeString(id))
            {
                Console.WriteLine("[数据库] Register fail, id not safe");
                return false;
            }
            if (!DbManager.IsSafeString(pw))
            {
                Console.WriteLine("[数据库] Register fail, pw not safe");
                return false;
            }
            //能否注册
            if (!IsAccountExist(id))
            {
                Console.WriteLine("[数据库] Register fail, id exist");
                return false;
            }
            //写入数据库User表
            string sql = string.Format("insert into account set id ='{0}' ,pw ='{1}';", id, pw);
            try
            {
                //Initializes a new instance of the MySqlCommand class with the text of the query and a MySqlConnection.
                MySqlCommand cmd = new MySqlCommand(sql, mySql);
                //Finishes asynchronous execution of a SQL statement.//执行ExecuteNonQuery 方法调用的过程更简单，
                //因为不需要创建存储结果的对象。这是因为ExecuteNonQuery 仅用于插入，更新和删除数据。
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] Register fail " + e.Message);
                return false;
            }
        }
        //创建角色 实际是对palyer数据可执行插入操作
        public static bool CreatePlayer(string id)
        {
            //防sql注入
            if (!DbManager.IsSafeString(id))
            {
                Console.WriteLine("[数据库] CreatePlayer fail, id not safe");
                return false;
            }
            //序列化
            PlayerData playerData = new PlayerData();
            string data = Js.Serialize(playerData);
            //写入数据库
            //传统方法 可能不安全 但只要前面判断过 防注入 就可以使用 也许有意外 具体不清楚
            //string sql = string.Format("insert into player set id ='{0}' ,data ='{1}';", id, data);

            //使用参数的方法 就输入的id、data标注为参数  
            //使用参数更安全，因为它们仅作为现场数据处理
            string sql = "INSERT INTO player (id,data) VALUES (@id,@data)";

            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mySql);
                cmd.Parameters.AddWithValue("@id", id.ToString());
                cmd.Parameters.AddWithValue("@data", data.ToString());
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] CreatePlayer err, " + e.Message);
                return false;
            }
        }
        //检测用户名密码
        public static bool CheckPassword(string id, string pw)
        {
            //防sql注入
            if (!DbManager.IsSafeString(id))
            {
                Console.WriteLine("[数据库] CheckPassword fail, id not safe");
                return false;
            }
            if (!DbManager.IsSafeString(pw))
            {
                Console.WriteLine("[数据库] CheckPassword fail, pw not safe");
                return false;
            }
            //查询
            //string sql = string.Format("select * from account where id='{0}' and pw='{1}';", id, pw);
            //使用参数
            string sql = "SELECT * FROM account WHERE (id , pw)=(@id,@pw);";

            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mySql);
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Parameters.AddWithValue("@pw", pw);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                bool hasRows = dataReader.HasRows;
                dataReader.Close();
                return hasRows;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] CheckPassword err, " + e.Message);
                return false;
            }
        }
        //获取玩家数据
        public static PlayerData GetPlayerData(string id)
        {
            //防sql注入
            if (!DbManager.IsSafeString(id))
            {
                Console.WriteLine("[数据库] GetPlayerData fail, id not safe");
                return null;
            }

            //sql
            //string sql = string.Format("select * from player where id ='{0}';", id);
            string sql = "SELECT * FROM player WHERE id=@id;";

            try
            {
                //查询
                MySqlCommand cmd = new MySqlCommand(sql, mySql);
                cmd.Parameters.AddWithValue("@id", id);
                MySqlDataReader dataReader = cmd.ExecuteReader();
                if (!dataReader.HasRows)
                {
                    dataReader.Close();
                    return null;
                }
                //读取
                dataReader.Read();
                string data = dataReader.GetString("data");
                //反序列化
                PlayerData playerData = Js.Deserialize<PlayerData>(data);
                dataReader.Close();
                return playerData;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] GetPlayerData fail, " + e.Message);
                return null;
            }
        }
        //保存角色
        public static bool UpdatePlayerData(string id, PlayerData playerData)
        {
            //序列化
            string data = Js.Serialize(playerData);
            //sql
            string sql = string.Format("update player set data='{0}' where id ='{1}';", data, id);
            //更新
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, mySql);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[数据库] UpdatePlayerData err, " + e.Message);
                return false;
            }
        }
        public static void test()
        {
            string data = "aglab101";
            DbManager.CreatePlayer(data);
            PlayerData pd = DbManager.GetPlayerData(data);
            pd.coin = 257;
            DbManager.UpdatePlayerData(data, pd);
        }
    }

}
